package com.supermarket.dao;

import com.supermarket.model.User;
import com.supermarket.util.DatabaseUtil;

import java.security.MessageDigest;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 用户数据访问对象
 * 负责用户相关的数据库操作
 */
public class UserDAO {

    /**
     * 用户登录验证
     * 
     * @param username 用户名
     * @param password 密码（明文）
     * @return 用户对象，登录失败返回null
     */
    public User login(String username, String password) {
        String sql = "SELECT u.id, u.username, u.real_name, u.phone, u.email, u.status, " +
                "       u.created_at, u.updated_at, r.role_name " +
                "FROM users u " +
                "JOIN user_roles ur ON u.id = ur.user_id " +
                "JOIN roles r ON ur.role_id = r.id " +
                "WHERE u.username = ? AND u.password_hash = ? AND u.status = 'ACTIVE'";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, username);
            ps.setString(2, md5(password)); // 使用MD5加密密码

            rs = ps.executeQuery();

            if (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setRealName(rs.getString("real_name"));
                user.setPhone(rs.getString("phone"));
                user.setEmail(rs.getString("email"));
                user.setStatus(rs.getString("status"));
                user.setCreatedAt(rs.getTimestamp("created_at"));
                user.setUpdatedAt(rs.getTimestamp("updated_at"));
                user.setRoleName(rs.getString("role_name"));
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return null;
    }

    /**
     * 根据用户ID获取用户信息
     * 
     * @param userId 用户ID
     * @return 用户对象
     */
    public User getUserById(int userId) {
        String sql = "SELECT u.id, u.username, u.real_name, u.phone, u.email, u.status, " +
                "       u.created_at, u.updated_at, r.role_name " +
                "FROM users u " +
                "JOIN user_roles ur ON u.id = ur.user_id " +
                "JOIN roles r ON ur.role_id = r.id " +
                "WHERE u.id = ?";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, userId);

            rs = ps.executeQuery();

            if (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setRealName(rs.getString("real_name"));
                user.setPhone(rs.getString("phone"));
                user.setEmail(rs.getString("email"));
                user.setStatus(rs.getString("status"));
                user.setCreatedAt(rs.getTimestamp("created_at"));
                user.setUpdatedAt(rs.getTimestamp("updated_at"));
                user.setRoleName(rs.getString("role_name"));
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return null;
    }

    /**
     * 获取所有用户列表
     * 
     * @return 用户列表
     */
    public List<User> getAllUsers() {
        List<User> users = new ArrayList<>();
        String sql = "SELECT u.id, u.username, u.real_name, u.phone, u.email, u.status, " +
                "       u.created_at, u.updated_at, r.role_name " +
                "FROM users u " +
                "JOIN user_roles ur ON u.id = ur.user_id " +
                "JOIN roles r ON ur.role_id = r.id " +
                "ORDER BY u.created_at DESC";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setRealName(rs.getString("real_name"));
                user.setPhone(rs.getString("phone"));
                user.setEmail(rs.getString("email"));
                user.setStatus(rs.getString("status"));
                user.setCreatedAt(rs.getTimestamp("created_at"));
                user.setUpdatedAt(rs.getTimestamp("updated_at"));
                user.setRoleName(rs.getString("role_name"));
                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return users;
    }

    /**
     * 添加新用户
     * 
     * @param user     用户对象
     * @param password 明文密码
     * @param roleId   角色ID
     * @return 是否添加成功
     */
    public boolean addUser(User user, String password, int roleId) {
        Connection conn = null;
        PreparedStatement ps1 = null;
        PreparedStatement ps2 = null;

        try {
            conn = DatabaseUtil.getConnection();
            conn.setAutoCommit(false); // 开启事务

            // 插入用户信息
            String sql1 = "INSERT INTO users (username, password_hash, real_name, phone, email, status) " +
                    "VALUES (?, ?, ?, ?, ?, ?)";
            ps1 = conn.prepareStatement(sql1, PreparedStatement.RETURN_GENERATED_KEYS);
            ps1.setString(1, user.getUsername());
            ps1.setString(2, md5(password));
            ps1.setString(3, user.getRealName());
            ps1.setString(4, user.getPhone());
            ps1.setString(5, user.getEmail());
            ps1.setString(6, "ACTIVE");

            int rows1 = ps1.executeUpdate();

            if (rows1 > 0) {
                // 获取生成的用户ID
                ResultSet rs = ps1.getGeneratedKeys();
                if (rs.next()) {
                    int userId = rs.getInt(1);

                    // 插入用户角色关联
                    String sql2 = "INSERT INTO user_roles (user_id, role_id) VALUES (?, ?)";
                    ps2 = conn.prepareStatement(sql2);
                    ps2.setInt(1, userId);
                    ps2.setInt(2, roleId);

                    int rows2 = ps2.executeUpdate();

                    if (rows2 > 0) {
                        conn.commit();
                        return true;
                    }
                }
            }

            conn.rollback();
            return false;

        } catch (SQLException e) {
            e.printStackTrace();
            try {
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            return false;
        } finally {
            try {
                if (conn != null) {
                    conn.setAutoCommit(true);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            DatabaseUtil.close(conn, ps1);
            DatabaseUtil.close(null, ps2);
        }
    }

    /**
     * 更新用户信息
     * 
     * @param user 用户对象
     * @return 是否更新成功
     */
    public boolean updateUser(User user) {
        String sql = "UPDATE users " +
                "SET real_name = ?, phone = ?, email = ?, updated_at = CURRENT_TIMESTAMP " +
                "WHERE id = ?";

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, user.getRealName());
            ps.setString(2, user.getPhone());
            ps.setString(3, user.getEmail());
            ps.setInt(4, user.getId());

            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.close(conn, ps);
        }
    }

    /**
     * 修改用户密码
     * 
     * @param userId      用户ID
     * @param newPassword 新密码（明文）
     * @return 是否修改成功
     */
    public boolean changePassword(int userId, String newPassword) {
        String sql = "UPDATE users SET password_hash = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?";

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, md5(newPassword));
            ps.setInt(2, userId);

            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.close(conn, ps);
        }
    }

    /**
     * 禁用用户
     * 
     * @param userId 用户ID
     * @return 是否禁用成功
     */
    public boolean disableUser(int userId) {
        String sql = "UPDATE users SET status = 'INACTIVE', updated_at = CURRENT_TIMESTAMP WHERE id = ?";

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, userId);

            return ps.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.close(conn, ps);
        }
    }

    /**
     * 检查用户名是否已存在
     * 
     * @param username 用户名
     * @return 是否存在
     */
    public boolean isUsernameExists(String username) {
        String sql = "SELECT COUNT(*) FROM users WHERE username = ?";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, username);
            rs = ps.executeQuery();

            if (rs.next()) {
                return rs.getInt(1) > 0;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }

        return false;
    }

    /**
     * 用户注册（注册为收银员角色）
     * 
     * @param username 用户名
     * @param password 密码（明文）
     * @param realName 真实姓名
     * @param phone    手机号
     * @param email    邮箱
     * @return 是否注册成功
     */
    public boolean register(String username, String password, String realName, String phone, String email) {
        // 检查用户名是否已存在
        if (isUsernameExists(username)) {
            return false;
        }

        // 创建用户对象
        User user = new User();
        user.setUsername(username);
        user.setRealName(realName);
        user.setPhone(phone);
        user.setEmail(email);

        // 注册为收银员角色 (角色ID为2)
        return addUser(user, password, 2);
    }

    /**
     * 重置用户密码
     * 
     * @param username    用户名
     * @param newPassword 新密码（明文）
     * @return 是否重置成功
     */
    public boolean resetPassword(String username, String newPassword) {
        String sql = "UPDATE users SET password_hash = ?, updated_at = CURRENT_TIMESTAMP WHERE username = ? AND status = 'ACTIVE'";

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, md5(newPassword)); // 使用MD5加密新密码
            ps.setString(2, username);

            int rowsAffected = ps.executeUpdate();
            return rowsAffected > 0;

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, null);
        }

        return false;
    }

    /**
     * 根据用户名获取用户信息
     */
    public User getUserByUsername(String username) {
        String sql = "SELECT u.id, u.username, u.real_name, u.phone, u.email, u.status, " +
                "       u.created_at, u.updated_at, r.role_name " +
                "FROM users u " +
                "JOIN user_roles ur ON u.id = ur.user_id " +
                "JOIN roles r ON ur.role_id = r.id " +
                "WHERE u.username = ?";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, username);

            rs = ps.executeQuery();

            if (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setRealName(rs.getString("real_name"));
                user.setPhone(rs.getString("phone"));
                user.setEmail(rs.getString("email"));
                user.setStatus(rs.getString("status"));
                user.setCreatedAt(rs.getTimestamp("created_at"));
                user.setUpdatedAt(rs.getTimestamp("updated_at"));
                user.setRoleName(rs.getString("role_name"));
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }
        return null;
    }

    /**
     * 更新用户信息（包含角色）
     */
    public boolean updateUser(User user, int roleId) {
        Connection conn = null;
        PreparedStatement ps = null;
        PreparedStatement psRole = null;

        try {
            conn = DatabaseUtil.getConnection();
            conn.setAutoCommit(false);

            // 更新用户基本信息
            String sql = "UPDATE users " +
                    "SET real_name = ?, phone = ?, email = ?, status = ?, updated_at = CURRENT_TIMESTAMP " +
                    "WHERE id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, user.getRealName());
            ps.setString(2, user.getPhone());
            ps.setString(3, user.getEmail());
            ps.setString(4, user.getStatus());
            ps.setInt(5, user.getId());

            int result = ps.executeUpdate();

            // 更新用户角色
            String roleSql = "UPDATE user_roles SET role_id = ? WHERE user_id = ?";
            psRole = conn.prepareStatement(roleSql);
            psRole.setInt(1, roleId);
            psRole.setInt(2, user.getId());
            psRole.executeUpdate();

            conn.commit();
            return result > 0;
        } catch (SQLException e) {
            try {
                if (conn != null) {
                    conn.rollback();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.close(conn, ps, null);
            DatabaseUtil.close(null, psRole, null);
        }
    }

    /**
     * 删除用户
     */
    public boolean deleteUser(String username) {
        String sql = "DELETE FROM users WHERE username = ?";

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, username);

            int result = ps.executeUpdate();
            return result > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.close(conn, ps, null);
        }
    }

    /**
     * 更新用户状态
     */
    public boolean updateUserStatus(String username, String status) {
        String sql = "UPDATE users SET status = ?, updated_at = CURRENT_TIMESTAMP WHERE username = ?";

        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, status);
            ps.setString(2, username);

            int result = ps.executeUpdate();
            return result > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } finally {
            DatabaseUtil.close(conn, ps, null);
        }
    }

    /**
     * 搜索用户
     */
    public List<User> searchUsers(String keyword) {
        List<User> users = new ArrayList<>();
        String sql = "SELECT u.id, u.username, u.real_name, u.phone, u.email, u.status, " +
                "       u.created_at, u.updated_at, r.role_name " +
                "FROM users u " +
                "JOIN user_roles ur ON u.id = ur.user_id " +
                "JOIN roles r ON ur.role_id = r.id " +
                "WHERE u.username LIKE ? OR u.real_name LIKE ? OR u.phone LIKE ? OR u.email LIKE ? " +
                "ORDER BY u.created_at DESC";

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DatabaseUtil.getConnection();
            ps = conn.prepareStatement(sql);
            String searchPattern = "%" + keyword + "%";
            ps.setString(1, searchPattern);
            ps.setString(2, searchPattern);
            ps.setString(3, searchPattern);
            ps.setString(4, searchPattern);

            rs = ps.executeQuery();

            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setRealName(rs.getString("real_name"));
                user.setPhone(rs.getString("phone"));
                user.setEmail(rs.getString("email"));
                user.setStatus(rs.getString("status"));
                user.setCreatedAt(rs.getTimestamp("created_at"));
                user.setUpdatedAt(rs.getTimestamp("updated_at"));
                user.setRoleName(rs.getString("role_name"));
                users.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseUtil.close(conn, ps, rs);
        }
        return users;
    }

    /**
     * MD5加密
     * 
     * @param input 输入字符串
     * @return MD5加密后的字符串
     */
    private String md5(String input) {
        try {
            MessageDigest md = MessageDigest.getInstance("MD5");
            byte[] digest = md.digest(input.getBytes());
            StringBuilder sb = new StringBuilder();
            for (byte b : digest) {
                sb.append(String.format("%02x", b));
            }
            return sb.toString();
        } catch (Exception e) {
            e.printStackTrace();
            return input; // 如果加密失败，返回原文
        }
    }
}
